package gentle.service.impl;

import com.baomidou.mybatisplus.plugins.Page;
import gentle.base.SqlMapper;
import gentle.base.StringUtil;
import gentle.entity.*;
import gentle.mapper.PerformanceDao;
import gentle.service.IPerformanceService;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
@Transactional
public class PerformanceServiceImpl implements IPerformanceService {
    @Autowired
    private SqlMapper sqlMapper;

    @Autowired
    private PerformanceDao dao;
    public List<PerformanceInfo> selectPageListQueryPages(Page<PerformanceInfo> page, SearchPerformanceInfo info) {
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("page", page);
        if (StringUtil.isNotEmpty(info.getStartRechargeTime())) {
            params.put("startRechargeTime", info.getStartRechargeTime());
        }
        if (StringUtil.isNotEmpty(info.getEndRechargeTime())) {
            params.put("endRechargeTime", info.getEndRechargeTime());
        }
        if (StringUtil.isNotEmpty(info.getOutpatientName())) {
            params.put("outpatientName", info.getOutpatientName());
        }
        if (StringUtil.isNotEmpty(info.getCustomerStatus())) {
            String customerStatus = "";
            String customerStatus2[] = info.getCustomerStatus().split(",");
            if (customerStatus2.length == 1) {
                customerStatus = customerStatus2[0];
            } else {
                for (int i = 0; i < customerStatus2.length; i++) {
                    if (i == (customerStatus2.length - 1)) {
                        customerStatus += "'" + customerStatus2[i];
                    } else if (i == 0) {
                        customerStatus += customerStatus2[i] + "'" + ",";
                    } else {
                        customerStatus += "'" + customerStatus2[i] + "'" + ",";
                    }
                }
            }
            params.put("customerStatus", " ('" + customerStatus + "')");
        }
        if (StringUtil.isNotEmpty(info.getIsDoName())) {
            if ("9".equals(info.getIsDoName())) {
                params.put("isDo", " '" + info.getIsDoName() + "' ");
                params.put("isEarnes", " and t1.isEarnes=2");
            }else if ("10".equals(info.getIsDoName())) {
                params.put("isDo", " '" + info.getIsDoName() + "' ");
                params.put("isEarnes", " and t1.isEarnes=10");
            } else {
                params.put("isDo", " '" + info.getIsDoName() + "'");
                params.put("isDos", " '9'");
            }
        }else{
            params.put("isEarnes", " and t1.isEarnes in (10,2) ");
        }
        if (StringUtil.isNotEmpty(info.getReach())) {
            params.put("reach", info.getReach());
        }
        if (StringUtil.isNotEmpty(info.getOneChannel())) {
            if (info.getOneChannel().indexOf("请选择") == -1) {
                String oneChannel = "";
                String oneChannel2[] = info.getOneChannel().split(",");
                if (oneChannel2.length == 1) {
                    oneChannel = oneChannel2[0];
                } else {
                    for (int i = 0; i < oneChannel2.length; i++) {
                        if (i == (oneChannel2.length - 1)) {
                            oneChannel += "'" + oneChannel2[i];
                        } else if (i == 0) {
                            oneChannel += oneChannel2[i] + "'" + ",";
                        } else {
                            oneChannel += "'" + oneChannel2[i] + "'" + ",";
                        }
                    }
                }
                params.put("oneChannel", " ('" + oneChannel + "')");
            }
        }
        if (StringUtil.isNotEmpty(info.getTwoChannel())) {
            if (info.getTwoChannel().indexOf("请选择") == -1) {
                String twoChannel = "";
                String twoChannel2[] = info.getTwoChannel().split(",");
                if (twoChannel2.length == 1) {
                    twoChannel = twoChannel2[0];
                } else {
                    for (int i = 0; i < twoChannel2.length; i++) {
                        if (i == (twoChannel2.length - 1)) {
                            twoChannel += "'" + twoChannel2[i];
                        } else if (i == 0) {
                            twoChannel += twoChannel2[i] + "'" + ",";
                        } else {
                            twoChannel += "'" + twoChannel2[i] + "'" + ",";
                        }
                    }
                }
                params.put("twoChannel", " ('" + twoChannel + "')");
            }
        }
        if (StringUtil.isNotEmpty(info.getThrChannel())) {
            if (info.getThrChannel().indexOf("请选择") == -1) {
                String thrChannel = "";
                String thrChannel2[] = info.getThrChannel().split(",");
                if (thrChannel2.length == 1) {
                    thrChannel = thrChannel2[0];
                } else {
                    for (int i = 0; i < thrChannel2.length; i++) {
                        if (i == (thrChannel2.length - 1)) {
                            thrChannel += "'" + thrChannel2[i];
                        } else if (i == 0) {
                            thrChannel += thrChannel2[i] + "'" + ",";
                        } else {
                            thrChannel += "'" + thrChannel2[i] + "'" + ",";
                        }
                    }
                }
                params.put("thrChannel", " ('" + thrChannel + "')");
            }
        }

        if (StringUtil.isNotEmpty(info.getOneMedium())) {
            if (info.getOneMedium().indexOf("请选择") == -1) {
                String oneMedium = "";
                String oneMedium2[] = info.getOneMedium().split(",");
                if (oneMedium2.length == 1) {
                    oneMedium = oneMedium2[0];
                } else {
                    for (int i = 0; i < oneMedium2.length; i++) {
                        if (i == (oneMedium2.length - 1)) {
                            oneMedium += "'" + oneMedium2[i];
                        } else if (i == 0) {
                            oneMedium += oneMedium2[i] + "'" + ",";
                        } else {
                            oneMedium += "'" + oneMedium2[i] + "'" + ",";
                        }
                    }
                }
                params.put("oneMedium", " ('" + oneMedium + "')");
            }
        }
        if (StringUtil.isNotEmpty(info.getTwoMedium())) {
            if (info.getTwoMedium().indexOf("请选择") == -1) {
                String twoMedium = "";
                String twoMedium2[] = info.getTwoMedium().split(",");
                if (twoMedium2.length == 1) {
                    twoMedium = twoMedium2[0];
                } else {
                    for (int i = 0; i < twoMedium2.length; i++) {
                        if (i == (twoMedium2.length - 1)) {
                            twoMedium += "'" + twoMedium2[i];
                        } else if (i == 0) {
                            twoMedium += twoMedium2[i] + "'" + ",";
                        } else {
                            twoMedium += "'" + twoMedium2[i] + "'" + ",";
                        }
                    }
                }
                params.put("twoMedium", " ('" + twoMedium + "')");
            }
        }
        if (StringUtil.isNotEmpty(info.getThrMedium())) {
            if (info.getThrMedium().indexOf("请选择") == -1) {
                String thrMedium = "";
                String thrMedium2[] = info.getThrMedium().split(",");
                if (thrMedium2.length == 1) {
                    thrMedium = thrMedium2[0];
                } else {
                    for (int i = 0; i < thrMedium2.length; i++) {
                        if (i == (thrMedium2.length - 1)) {
                            thrMedium += "'" + thrMedium2[i];
                        } else if (i == 0) {
                            thrMedium += thrMedium2[i] + "'" + ",";
                        } else {
                            thrMedium += "'" + thrMedium2[i] + "'" + ",";
                        }
                    }
                }
                params.put("thrMedium", " ('" + thrMedium + "')");
            }
        }
        if (StringUtil.isNotEmpty(info.getTreatmentUnit())) {
            String treatmentUnit = "";
            String treatmentUnit2[] = info.getTreatmentUnit().split(",");
            if (treatmentUnit2.length == 1) {
                treatmentUnit = treatmentUnit2[0];
            } else {
                for (int i = 0; i < treatmentUnit2.length; i++) {
                    if (i == (treatmentUnit2.length - 1)) {
                        treatmentUnit += "'" + treatmentUnit2[i];
                    } else if (i == 0) {
                        treatmentUnit += treatmentUnit2[i] + "'" + ",";
                    } else {
                        treatmentUnit += "'" + treatmentUnit2[i] + "'" + ",";
                    }
                }
            }
            params.put("treatmentUnit", " ('" + treatmentUnit + "')");
        }
        if (StringUtil.isNotEmpty(info.getScarchiving())) {
            String scarchiving = "";
            String scarchiving2[] = info.getScarchiving().split(",");
            if (scarchiving2.length == 1) {
                scarchiving = scarchiving2[0];
            } else {
                for (int i = 0; i < scarchiving2.length; i++) {
                    if (i == (scarchiving2.length - 1)) {
                        scarchiving += "'" + scarchiving2[i];
                    } else if (i == 0) {
                        scarchiving += scarchiving2[i] + "'" + ",";
                    } else {
                        scarchiving += "'" + scarchiving2[i] + "'" + ",";
                    }
                }
            }
            params.put("scarchiving", " ('" + scarchiving + "')");
        }
        if (StringUtil.isNotEmpty(info.getDoctor())) {
            String doctor = "";
            String doctor2[] = info.getDoctor().split(",");
            if (doctor2.length == 1) {
                doctor = doctor2[0];
            } else {
                for (int i = 0; i < doctor2.length; i++) {
                    if (i == (doctor2.length - 1)) {
                        doctor += "'" + doctor2[i];
                    } else if (i == 0) {
                        doctor += doctor2[i] + "'" + ",";
                    } else {
                        doctor += "'" + doctor2[i] + "'" + ",";
                    }
                }
            }
            params.put("doctor", " ('" + doctor + "')");
        }
        if (StringUtil.isNotEmpty(info.getTcName())) {
            String tcName = "";
            String tcName2[] = info.getTcName().split(",");
            if (tcName2.length == 1) {
                tcName = tcName2[0];
            } else {
                for (int i = 0; i < tcName2.length; i++) {
                    if (i == (tcName2.length - 1)) {
                        tcName += "'" + tcName2[i];
                    } else if (i == 0) {
                        tcName += tcName2[i] + "'" + ",";
                    } else {
                        tcName += "'" + tcName2[i] + "'" + ",";
                    }
                }
            }
            params.put("tcName", " ('" + tcName + "')");
        }
        String tableHead = info.getAbbreviation();
        String tableName1 = tableHead + "deal_info";
        String tableName2 = tableHead + "recharge_info";
        params.put("tableName", tableHead+"customer_info");
        params.put("tableName1", tableName1);
        params.put("tableName2", tableName2);
        params.put("startIndex", info.getStartIndex());
        params.put("limitIndex", info.getLimitIndex());
        return dao.getQueryPageAchievementList(page, params);
    }

    public int getQueryPagesCount(Page<PerformanceInfo> page, SearchPerformanceInfo info) {
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("page", page);
        if (StringUtil.isNotEmpty(info.getStartRechargeTime())) {
            params.put("startRechargeTime", info.getStartRechargeTime());
        }
        if (StringUtil.isNotEmpty(info.getEndRechargeTime())) {
            params.put("endRechargeTime", info.getEndRechargeTime());
        }
        if (StringUtil.isNotEmpty(info.getOutpatientName())) {
            params.put("outpatientName", info.getOutpatientName());
        }
        if (StringUtil.isNotEmpty(info.getCustomerStatus())) {
            String customerStatus = "";
            String customerStatus2[] = info.getCustomerStatus().split(",");
            if (customerStatus2.length == 1) {
                customerStatus = customerStatus2[0];
            } else {
                for (int i = 0; i < customerStatus2.length; i++) {
                    if (i == (customerStatus2.length - 1)) {
                        customerStatus += "'" + customerStatus2[i];
                    } else if (i == 0) {
                        customerStatus += customerStatus2[i] + "'" + ",";
                    } else {
                        customerStatus += "'" + customerStatus2[i] + "'" + ",";
                    }
                }
            }
            params.put("customerStatus", " ('" + customerStatus + "')");
        }
        if (StringUtil.isNotEmpty(info.getIsDoName())) {
            if ("9".equals(info.getIsDoName())) {
                params.put("isDo", " '" + info.getIsDoName() + "' ");
                params.put("isEarnes", " and t1.isEarnes=2");
            }else if ("10".equals(info.getIsDoName())) {
                params.put("isDo", " '" + info.getIsDoName() + "' ");
                params.put("isEarnes", " and t1.isEarnes=10");
            } else {
                params.put("isDo", " '" + info.getIsDoName() + "'");
                params.put("isDos", " '9'");
            }
        }else{
            params.put("isEarnes", " and t1.isEarnes in (10,2) ");
        }
        if (StringUtil.isNotEmpty(info.getReach())) {
            params.put("reach", info.getReach());
        }
        if (StringUtil.isNotEmpty(info.getOneChannel())) {
            if (info.getOneChannel().indexOf("请选择") == -1) {
                String oneChannel = "";
                String oneChannel2[] = info.getOneChannel().split(",");
                if (oneChannel2.length == 1) {
                    oneChannel = oneChannel2[0];
                } else {
                    for (int i = 0; i < oneChannel2.length; i++) {
                        if (i == (oneChannel2.length - 1)) {
                            oneChannel += "'" + oneChannel2[i];
                        } else if (i == 0) {
                            oneChannel += oneChannel2[i] + "'" + ",";
                        } else {
                            oneChannel += "'" + oneChannel2[i] + "'" + ",";
                        }
                    }
                }
                params.put("oneChannel", " ('" + oneChannel + "')");
            }
        }
        if (StringUtil.isNotEmpty(info.getTwoChannel())) {
            if (info.getTwoChannel().indexOf("请选择") == -1) {
                String twoChannel = "";
                String twoChannel2[] = info.getTwoChannel().split(",");
                if (twoChannel2.length == 1) {
                    twoChannel = twoChannel2[0];
                } else {
                    for (int i = 0; i < twoChannel2.length; i++) {
                        if (i == (twoChannel2.length - 1)) {
                            twoChannel += "'" + twoChannel2[i];
                        } else if (i == 0) {
                            twoChannel += twoChannel2[i] + "'" + ",";
                        } else {
                            twoChannel += "'" + twoChannel2[i] + "'" + ",";
                        }
                    }
                }
                params.put("twoChannel", " ('" + twoChannel + "')");
            }
        }
        if (StringUtil.isNotEmpty(info.getThrChannel())) {
            if (info.getThrChannel().indexOf("请选择") == -1) {
                String thrChannel = "";
                String thrChannel2[] = info.getThrChannel().split(",");
                if (thrChannel2.length == 1) {
                    thrChannel = thrChannel2[0];
                } else {
                    for (int i = 0; i < thrChannel2.length; i++) {
                        if (i == (thrChannel2.length - 1)) {
                            thrChannel += "'" + thrChannel2[i];
                        } else if (i == 0) {
                            thrChannel += thrChannel2[i] + "'" + ",";
                        } else {
                            thrChannel += "'" + thrChannel2[i] + "'" + ",";
                        }
                    }
                }
                params.put("thrChannel", " ('" + thrChannel + "')");
            }
        }

        if (StringUtil.isNotEmpty(info.getOneMedium())) {
            if (info.getOneMedium().indexOf("请选择") == -1) {
                String oneMedium = "";
                String oneMedium2[] = info.getOneMedium().split(",");
                if (oneMedium2.length == 1) {
                    oneMedium = oneMedium2[0];
                } else {
                    for (int i = 0; i < oneMedium2.length; i++) {
                        if (i == (oneMedium2.length - 1)) {
                            oneMedium += "'" + oneMedium2[i];
                        } else if (i == 0) {
                            oneMedium += oneMedium2[i] + "'" + ",";
                        } else {
                            oneMedium += "'" + oneMedium2[i] + "'" + ",";
                        }
                    }
                }
                params.put("oneMedium", " ('" + oneMedium + "')");
            }
        }
        if (StringUtil.isNotEmpty(info.getTwoMedium())) {
            if (info.getTwoMedium().indexOf("请选择") == -1) {
                String twoMedium = "";
                String twoMedium2[] = info.getTwoMedium().split(",");
                if (twoMedium2.length == 1) {
                    twoMedium = twoMedium2[0];
                } else {
                    for (int i = 0; i < twoMedium2.length; i++) {
                        if (i == (twoMedium2.length - 1)) {
                            twoMedium += "'" + twoMedium2[i];
                        } else if (i == 0) {
                            twoMedium += twoMedium2[i] + "'" + ",";
                        } else {
                            twoMedium += "'" + twoMedium2[i] + "'" + ",";
                        }
                    }
                }
                params.put("twoMedium", " ('" + twoMedium + "')");
            }
        }
        if (StringUtil.isNotEmpty(info.getThrMedium())) {
            if (info.getThrMedium().indexOf("请选择") == -1) {
                String thrMedium = "";
                String thrMedium2[] = info.getThrMedium().split(",");
                if (thrMedium2.length == 1) {
                    thrMedium = thrMedium2[0];
                } else {
                    for (int i = 0; i < thrMedium2.length; i++) {
                        if (i == (thrMedium2.length - 1)) {
                            thrMedium += "'" + thrMedium2[i];
                        } else if (i == 0) {
                            thrMedium += thrMedium2[i] + "'" + ",";
                        } else {
                            thrMedium += "'" + thrMedium2[i] + "'" + ",";
                        }
                    }
                }
                params.put("thrMedium", " ('" + thrMedium + "')");
            }
        }
        if (StringUtil.isNotEmpty(info.getTreatmentUnit())) {
            String treatmentUnit = "";
            String treatmentUnit2[] = info.getTreatmentUnit().split(",");
            if (treatmentUnit2.length == 1) {
                treatmentUnit = treatmentUnit2[0];
            } else {
                for (int i = 0; i < treatmentUnit2.length; i++) {
                    if (i == (treatmentUnit2.length - 1)) {
                        treatmentUnit += "'" + treatmentUnit2[i];
                    } else if (i == 0) {
                        treatmentUnit += treatmentUnit2[i] + "'" + ",";
                    } else {
                        treatmentUnit += "'" + treatmentUnit2[i] + "'" + ",";
                    }
                }
            }
            params.put("treatmentUnit", " ('" + treatmentUnit + "')");
        }
        if (StringUtil.isNotEmpty(info.getScarchiving())) {
            String scarchiving = "";
            String scarchiving2[] = info.getScarchiving().split(",");
            if (scarchiving2.length == 1) {
                scarchiving = scarchiving2[0];
            } else {
                for (int i = 0; i < scarchiving2.length; i++) {
                    if (i == (scarchiving2.length - 1)) {
                        scarchiving += "'" + scarchiving2[i];
                    } else if (i == 0) {
                        scarchiving += scarchiving2[i] + "'" + ",";
                    } else {
                        scarchiving += "'" + scarchiving2[i] + "'" + ",";
                    }
                }
            }
            params.put("scarchiving", " ('" + scarchiving + "')");
        }
        if (StringUtil.isNotEmpty(info.getDoctor())) {
            String doctor = "";
            String doctor2[] = info.getDoctor().split(",");
            if (doctor2.length == 1) {
                doctor = doctor2[0];
            } else {
                for (int i = 0; i < doctor2.length; i++) {
                    if (i == (doctor2.length - 1)) {
                        doctor += "'" + doctor2[i];
                    } else if (i == 0) {
                        doctor += doctor2[i] + "'" + ",";
                    } else {
                        doctor += "'" + doctor2[i] + "'" + ",";
                    }
                }
            }
            params.put("doctor", " ('" + doctor + "')");
        }
        if (StringUtil.isNotEmpty(info.getTcName())) {
            String tcName = "";
            String tcName2[] = info.getTcName().split(",");
            if (tcName2.length == 1) {
                tcName = tcName2[0];
            } else {
                for (int i = 0; i < tcName2.length; i++) {
                    if (i == (tcName2.length - 1)) {
                        tcName += "'" + tcName2[i];
                    } else if (i == 0) {
                        tcName += tcName2[i] + "'" + ",";
                    } else {
                        tcName += "'" + tcName2[i] + "'" + ",";
                    }
                }
            }
            params.put("tcName", " ('" + tcName + "')");
        }
        String tableHead = info.getAbbreviation();
        String tableName1 = tableHead + "deal_info";
        String tableName2 = tableHead + "recharge_info";
        params.put("tableName",  tableHead + "customer_info");
        params.put("tableName1", tableName1);
        params.put("tableName2", tableName2);
        return dao.getQueryPagesCount(page, params);
    }

    public PerformanceInfo total(String reach, String startRechargeTime, String endRechargeTime, String oneChannel1, String twoChannel1,
                                 String thrChannel1, String oneMedium1, String twoMedium1, String thrMedium1, String scarchiving1, String tcName1, String outpatientName,
                                 String isDoName1, String treatmentUnit1, String customerStatus1){
        String getTableHeadsql = "select abbreviation from dept t where t.simplename='" + outpatientName + "' limit 1";
        Deptinfos deptinfos = sqlMapper.selectOne(getTableHeadsql, Deptinfos.class);
        String tableHead = deptinfos.getAbbreviation();
        String tableNamec = tableHead + "customer_info";
        String tableNamed = tableHead + "deal_info";
        String tableNamer = tableHead + "recharge_info";
        String contitionInfo = " ";
        String contitionInfos = " ";
        if (StringUtil.isNotEmpty(startRechargeTime)) {
            contitionInfo += " and t1.recharge_time >='" + startRechargeTime + "'";
        }
        if (StringUtil.isNotEmpty(endRechargeTime)) {
            contitionInfo += " and t1.recharge_time <='" + endRechargeTime + "'";
        }
        if (StringUtil.isNotEmpty(outpatientName)) {
            contitionInfo += " and t.arrival_store ='" + outpatientName + "'";
        }
        if (StringUtil.isNotEmpty(customerStatus1)) {
            String customerStatus = "";
            String customerStatus2[] = customerStatus1.split(",");
            if (customerStatus2.length == 1) {
                customerStatus = customerStatus2[0];
            } else {
                for (int i = 0; i < customerStatus2.length; i++) {
                    if (i == (customerStatus2.length - 1)) {
                        customerStatus += "'" + customerStatus2[i];
                    } else if (i == 0) {
                        customerStatus += customerStatus2[i] + "'" + ",";
                    } else {
                        customerStatus += "'" + customerStatus2[i] + "'" + ",";
                    }
                }
            }
            contitionInfo += " and t.customer_status in ('" + customerStatus + "')";
        }
        String isEarnes=" ";
        String isEarnes1=" ";
        if (StringUtil.isNotEmpty(isDoName1)) {
            if ("9".equals(isDoName1)) {
                isEarnes +=" and (t1.isEarnes=2 or t1.isEarnes=10)";
                isEarnes1 +=" and t1.isEarnes=10";
            }else  if ("10".equals(isDoName1)) {
                isEarnes +=" and t1.isEarnes=10";
                isEarnes1 +=" and t1.isEarnes=10";
            } else{
                contitionInfo += " and t2.is_do = '" + isDoName1 + "'";
                contitionInfos += " and t2.is_do = '9'";
            }
        }else{
            isEarnes +=" and t1.isEarnes in (2,10)";
        }
        if (StringUtil.isNotEmpty(oneChannel1)) {
            if (oneChannel1.indexOf("请选择") == -1) {
                String oneChannel = "";
                String oneChannel2[] = oneChannel1.split(",");
                if (oneChannel2.length == 1) {
                    oneChannel = oneChannel2[0];
                } else {
                    for (int i = 0; i < oneChannel2.length; i++) {
                        if (i == (oneChannel2.length - 1)) {
                            oneChannel += "'" + oneChannel2[i];
                        } else if (i == 0) {
                            oneChannel += oneChannel2[i] + "'" + ",";
                        } else {
                            oneChannel += "'" + oneChannel2[i] + "'" + ",";
                        }
                    }
                }
                contitionInfo += " and t.one_channel in ('" + oneChannel + "')";
            }
        }
        if (StringUtil.isNotEmpty(twoChannel1)) {
            if (twoChannel1.indexOf("请选择") == -1) {
                String twoChannel = "";
                String twoChannel2[] = twoChannel1.split(",");
                if (twoChannel2.length == 1) {
                    twoChannel = twoChannel2[0];
                } else {
                    for (int i = 0; i < twoChannel2.length; i++) {
                        if (i == (twoChannel2.length - 1)) {
                            twoChannel += "'" + twoChannel2[i];
                        } else if (i == 0) {
                            twoChannel += twoChannel2[i] + "'" + ",";
                        } else {
                            twoChannel += "'" + twoChannel2[i] + "'" + ",";
                        }
                    }
                }
                contitionInfo += " and t.two_channel in ('" + twoChannel + "')";
            }
        }
        if (StringUtil.isNotEmpty(thrChannel1)) {
            if (thrChannel1.indexOf("请选择") == -1) {
                String thrChannel = "";
                String thrChannel2[] = thrChannel1.split(",");
                if (thrChannel2.length == 1) {
                    thrChannel = thrChannel2[0];
                } else {
                    for (int i = 0; i < thrChannel2.length; i++) {
                        if (i == (thrChannel2.length - 1)) {
                            thrChannel += "'" + thrChannel2[i];
                        } else if (i == 0) {
                            thrChannel += thrChannel2[i] + "'" + ",";
                        } else {
                            thrChannel += "'" + thrChannel2[i] + "'" + ",";
                        }
                    }
                }
                contitionInfo += " and t.thr_channel in ('" + thrChannel + "')";
            }
        }
        if (StringUtil.isNotEmpty(oneMedium1)) {
            if (oneMedium1.indexOf("请选择") == -1) {
                String oneMedium = "";
                String oneMedium2[] = oneMedium1.split(",");
                if (oneMedium2.length == 1) {
                    oneMedium = oneMedium2[0];
                } else {
                    for (int i = 0; i < oneMedium2.length; i++) {
                        if (i == (oneMedium2.length - 1)) {
                            oneMedium += "'" + oneMedium2[i];
                        } else if (i == 0) {
                            oneMedium += oneMedium2[i] + "'" + ",";
                        } else {
                            oneMedium += "'" + oneMedium2[i] + "'" + ",";
                        }
                    }
                }
                contitionInfo += " and t.one_Medium in ('" + oneMedium + "')";
            }
        }
        if (StringUtil.isNotEmpty(twoMedium1)) {
            if (twoMedium1.indexOf("请选择") == -1) {
                String twoMedium = "";
                String twoMedium2[] = twoMedium1.split(",");
                if (twoMedium2.length == 1) {
                    twoMedium = twoMedium2[0];
                } else {
                    for (int i = 0; i < twoMedium2.length; i++) {
                        if (i == (twoMedium2.length - 1)) {
                            twoMedium += "'" + twoMedium2[i];
                        } else if (i == 0) {
                            twoMedium += twoMedium2[i] + "'" + ",";
                        } else {
                            twoMedium += "'" + twoMedium2[i] + "'" + ",";
                        }
                    }
                }
                contitionInfo += " and t.two_Medium in ('" + twoMedium + "')";
            }
        }
        if (StringUtil.isNotEmpty(thrMedium1)) {
            if (thrMedium1.indexOf("请选择") == -1) {
                String thrMedium = "";
                String thrMedium2[] = thrMedium1.split(",");
                if (thrMedium2.length == 1) {
                    thrMedium = thrMedium2[0];
                } else {
                    for (int i = 0; i < thrMedium2.length; i++) {
                        if (i == (thrMedium2.length - 1)) {
                            thrMedium += "'" + thrMedium2[i];
                        } else if (i == 0) {
                            thrMedium += thrMedium2[i] + "'" + ",";
                        } else {
                            thrMedium += "'" + thrMedium2[i] + "'" + ",";
                        }
                    }
                }
                contitionInfo += " and t.thr_Medium in ('" + thrMedium + "')";
            }
        }
        if (StringUtil.isNotEmpty(treatmentUnit1)) {
            String treatmentUnit = "";
            String treatmentUnit2[] = treatmentUnit1.split(",");
            if (treatmentUnit2.length == 1) {
                treatmentUnit = treatmentUnit2[0];
            } else {
                for (int i = 0; i < treatmentUnit2.length; i++) {
                    if (i == (treatmentUnit2.length - 1)) {
                        treatmentUnit += "'" + treatmentUnit2[i];
                    } else if (i == 0) {
                        treatmentUnit += treatmentUnit2[i] + "'" + ",";
                    } else {
                        treatmentUnit += "'" + treatmentUnit2[i] + "'" + ",";
                    }
                }
            }
            contitionInfo += " and t.treatment_unit in ('" + treatmentUnit + "')";
        }
        if (StringUtil.isNotEmpty(scarchiving1)) {
            String scarchiving = "";
            String scarchiving2[] = scarchiving1.split(",");
            if (scarchiving2.length == 1) {
                scarchiving = scarchiving2[0];
            } else {
                for (int i = 0; i < scarchiving2.length; i++) {
                    if (i == (scarchiving2.length - 1)) {
                        scarchiving += "'" + scarchiving2[i];
                    } else if (i == 0) {
                        scarchiving += scarchiving2[i] + "'" + ",";
                    } else {
                        scarchiving += "'" + scarchiving2[i] + "'" + ",";
                    }
                }
            }
            contitionInfo += " and t.scarchiving in ('" + scarchiving + "')";
        }
        if (StringUtil.isNotEmpty(tcName1)) {
            String tcName = "";
            String tcName2[] = tcName1.split(",");
            if (tcName2.length == 1) {
                tcName = tcName2[0];
            } else {
                for (int i = 0; i < tcName2.length; i++) {
                    if (i == (tcName2.length - 1)) {
                        tcName += "'" + tcName2[i];
                    } else if (i == 0) {
                        tcName += tcName2[i] + "'" + ",";
                    } else {
                        tcName += "'" + tcName2[i] + "'" + ",";
                    }
                }
            }
            contitionInfo += " and (t2.tc_name in ('" + tcName + "') or t1.tc_name in ('" + tcName + "'))";
        }
        if (StringUtils.isNotBlank(reach)) {
            contitionInfo += " and (t.customer_name like CONCAT('%','" + reach + "','%') or t.customer_phone like CONCAT('%','" + reach + "','%') " +
                    " or t.wechat_number like CONCAT('%','" + reach + "','%') or t.medical_record_number like CONCAT('%','" + reach + "','%') " +
                    " or t.szm like CONCAT('%','" + reach + "','%') or t.customer_phone2 like CONCAT('%','" + reach + "','%'))";
        }
        String sql = " " +
                "select ifnull(sum(serviceActualPay),0) as hospitalPerformance,ifnull(sum(serviceActualPay)+sum(depositAmount),0) as sectionPerformance," +
                "   count(distinct(t.reservationsId)) as number\n" +
                "  from (select t.reservationsId as reservationsId,t.getTime as getTime,\n" +
                "   sum(t.serviceActualPay) as serviceActualPay,\n" +
                "   sum(t.depositAmount) as depositAmount\n" +
                "   from (select t1.reservations_id as reservationsId,t2.get_time as getTime,\n" +
                "   t2.service_actual_pay as serviceActualPay,\n" +
                "   t2.deposit_amount as depositAmount\n" +
                "   from "+tableNamed+" t2\n" +
                "   left join "+tableNamer+" t1 on t1.deal_id=t2.id\n" +
                "   left join "+tableNamec+" t on t.id=t1.customer_id\n" +
                "   where 1=1\n" +isEarnes1+
                "   "+contitionInfo+"" +
                "   group by t2.id) t group by t.getTime,t.reservationsId\n" +
                "   ) t ; ";
        PerformanceInfo tongJiInfoList1 = sqlMapper.selectOne(sql, PerformanceInfo.class);
        String sql2 = " " +
                "select ifnull(sum(serviceActualPay),0) as hospitalPerformance ,0 as sectionPerformance,count(distinct(t.reservationsId)) as number\n" +
                "  from (select t.reservationsId as reservationsId,t.getTime as getTime,\n" +
                "   sum(t.serviceActualPay) as serviceActualPay,\n" +
                "   sum(t.depositAmount) as depositAmount\n" +
                "   from ("+
                "   select t.reservations_id as reservationsId,'' as getTime,\n" +
                "   t1.actual_account_balance as serviceActualPay,\n" +
                "   0 as depositAmount\n" +
                "   from "+tableNamer+" t1\n" +
                "   left join "+tableNamec+" t on t.id=t1.customer_id\n" +
                "   left join "+tableNamed+" t2 on t.id=t2.customer_id\n" +
                "   where 1=1  " +isEarnes+
                "   "+contitionInfo+"" +
                "   "+contitionInfos+"" +
                "   group by t1.id) t) t ; ";
        PerformanceInfo tongJiInfoList2 = sqlMapper.selectOne(sql2, PerformanceInfo.class);
        PerformanceInfo tongJiInfo = new PerformanceInfo();
        tongJiInfo.setHospitalPerformance(tongJiInfoList1.getHospitalPerformance()+tongJiInfoList2.getHospitalPerformance());
        tongJiInfo.setSectionPerformance(tongJiInfoList1.getSectionPerformance()+tongJiInfoList2.getSectionPerformance());
        tongJiInfo.setNumber(tongJiInfoList1.getNumber()+tongJiInfoList2.getNumber());
        return tongJiInfo;
    }

    public List<PerformanceInfo> getperformanceTableExport(SearchPerformanceInfo info) {
        Map<String, Object> params = new HashMap<String, Object>();
        if (StringUtil.isNotEmpty(info.getStartRechargeTime())) {
            params.put("startRechargeTime", info.getStartRechargeTime());
        }
        if (StringUtil.isNotEmpty(info.getEndRechargeTime())) {
            params.put("endRechargeTime", info.getEndRechargeTime());
        }
        if (StringUtil.isNotEmpty(info.getOutpatientName())) {
            params.put("outpatientName", info.getOutpatientName());
        }
        if (StringUtil.isNotEmpty(info.getCustomerStatus())) {
            String customerStatus = "";
            String customerStatus2[] = info.getCustomerStatus().split(",");
            if (customerStatus2.length == 1) {
                customerStatus = customerStatus2[0];
            } else {
                for (int i = 0; i < customerStatus2.length; i++) {
                    if (i == (customerStatus2.length - 1)) {
                        customerStatus += "'" + customerStatus2[i];
                    } else if (i == 0) {
                        customerStatus += customerStatus2[i] + "'" + ",";
                    } else {
                        customerStatus += "'" + customerStatus2[i] + "'" + ",";
                    }
                }
            }
            params.put("customerStatus", " ('" + customerStatus + "')");
        }
        if (StringUtil.isNotEmpty(info.getIsDoName())) {
            if ("9".equals(info.getIsDoName())) {
                params.put("isDo", " '" + info.getIsDoName() + "' ");
                params.put("isEarnes", " and t1.isEarnes=2");
            }else if ("10".equals(info.getIsDoName())) {
                params.put("isDo", " '" + info.getIsDoName() + "' ");
                params.put("isEarnes", " and t1.isEarnes=10");
            } else {
                params.put("isDo", " '" + info.getIsDoName() + "'");
                params.put("isDos", " '9'");
            }
        }else{
            params.put("isEarnes", " and t1.isEarnes in (10,2) ");
        }
        if (StringUtil.isNotEmpty(info.getReach())) {
            params.put("reach", info.getReach());
        }
        if (StringUtil.isNotEmpty(info.getOneChannel())) {
            if (info.getOneChannel().indexOf("请选择") == -1) {
                String oneChannel = "";
                String oneChannel2[] = info.getOneChannel().split(",");
                if (oneChannel2.length == 1) {
                    oneChannel = oneChannel2[0];
                } else {
                    for (int i = 0; i < oneChannel2.length; i++) {
                        if (i == (oneChannel2.length - 1)) {
                            oneChannel += "'" + oneChannel2[i];
                        } else if (i == 0) {
                            oneChannel += oneChannel2[i] + "'" + ",";
                        } else {
                            oneChannel += "'" + oneChannel2[i] + "'" + ",";
                        }
                    }
                }
                params.put("oneChannel", " ('" + oneChannel + "')");
            }
        }
        if (StringUtil.isNotEmpty(info.getTwoChannel())) {
            if (info.getTwoChannel().indexOf("请选择") == -1) {
                String twoChannel = "";
                String twoChannel2[] = info.getTwoChannel().split(",");
                if (twoChannel2.length == 1) {
                    twoChannel = twoChannel2[0];
                } else {
                    for (int i = 0; i < twoChannel2.length; i++) {
                        if (i == (twoChannel2.length - 1)) {
                            twoChannel += "'" + twoChannel2[i];
                        } else if (i == 0) {
                            twoChannel += twoChannel2[i] + "'" + ",";
                        } else {
                            twoChannel += "'" + twoChannel2[i] + "'" + ",";
                        }
                    }
                }
                params.put("twoChannel", " ('" + twoChannel + "')");
            }
        }
        if (StringUtil.isNotEmpty(info.getThrChannel())) {
            if (info.getThrChannel().indexOf("请选择") == -1) {
                String thrChannel = "";
                String thrChannel2[] = info.getThrChannel().split(",");
                if (thrChannel2.length == 1) {
                    thrChannel = thrChannel2[0];
                } else {
                    for (int i = 0; i < thrChannel2.length; i++) {
                        if (i == (thrChannel2.length - 1)) {
                            thrChannel += "'" + thrChannel2[i];
                        } else if (i == 0) {
                            thrChannel += thrChannel2[i] + "'" + ",";
                        } else {
                            thrChannel += "'" + thrChannel2[i] + "'" + ",";
                        }
                    }
                }
                params.put("thrChannel", " ('" + thrChannel + "')");
            }
        }

        if (StringUtil.isNotEmpty(info.getOneMedium())) {
            if (info.getOneMedium().indexOf("请选择") == -1) {
                String oneMedium = "";
                String oneMedium2[] = info.getOneMedium().split(",");
                if (oneMedium2.length == 1) {
                    oneMedium = oneMedium2[0];
                } else {
                    for (int i = 0; i < oneMedium2.length; i++) {
                        if (i == (oneMedium2.length - 1)) {
                            oneMedium += "'" + oneMedium2[i];
                        } else if (i == 0) {
                            oneMedium += oneMedium2[i] + "'" + ",";
                        } else {
                            oneMedium += "'" + oneMedium2[i] + "'" + ",";
                        }
                    }
                }
                params.put("oneMedium", " ('" + oneMedium + "')");
            }
        }
        if (StringUtil.isNotEmpty(info.getTwoMedium())) {
            if (info.getTwoMedium().indexOf("请选择") == -1) {
                String twoMedium = "";
                String twoMedium2[] = info.getTwoMedium().split(",");
                if (twoMedium2.length == 1) {
                    twoMedium = twoMedium2[0];
                } else {
                    for (int i = 0; i < twoMedium2.length; i++) {
                        if (i == (twoMedium2.length - 1)) {
                            twoMedium += "'" + twoMedium2[i];
                        } else if (i == 0) {
                            twoMedium += twoMedium2[i] + "'" + ",";
                        } else {
                            twoMedium += "'" + twoMedium2[i] + "'" + ",";
                        }
                    }
                }
                params.put("twoMedium", " ('" + twoMedium + "')");
            }
        }
        if (StringUtil.isNotEmpty(info.getThrMedium())) {
            if (info.getThrMedium().indexOf("请选择") == -1) {
                String thrMedium = "";
                String thrMedium2[] = info.getThrMedium().split(",");
                if (thrMedium2.length == 1) {
                    thrMedium = thrMedium2[0];
                } else {
                    for (int i = 0; i < thrMedium2.length; i++) {
                        if (i == (thrMedium2.length - 1)) {
                            thrMedium += "'" + thrMedium2[i];
                        } else if (i == 0) {
                            thrMedium += thrMedium2[i] + "'" + ",";
                        } else {
                            thrMedium += "'" + thrMedium2[i] + "'" + ",";
                        }
                    }
                }
                params.put("thrMedium", " ('" + thrMedium + "')");
            }
        }
        if (StringUtil.isNotEmpty(info.getTreatmentUnit())) {
            String treatmentUnit = "";
            String treatmentUnit2[] = info.getTreatmentUnit().split(",");
            if (treatmentUnit2.length == 1) {
                treatmentUnit = treatmentUnit2[0];
            } else {
                for (int i = 0; i < treatmentUnit2.length; i++) {
                    if (i == (treatmentUnit2.length - 1)) {
                        treatmentUnit += "'" + treatmentUnit2[i];
                    } else if (i == 0) {
                        treatmentUnit += treatmentUnit2[i] + "'" + ",";
                    } else {
                        treatmentUnit += "'" + treatmentUnit2[i] + "'" + ",";
                    }
                }
            }
            params.put("treatmentUnit", " ('" + treatmentUnit + "')");
        }
        if (StringUtil.isNotEmpty(info.getScarchiving())) {
            String scarchiving = "";
            String scarchiving2[] = info.getScarchiving().split(",");
            if (scarchiving2.length == 1) {
                scarchiving = scarchiving2[0];
            } else {
                for (int i = 0; i < scarchiving2.length; i++) {
                    if (i == (scarchiving2.length - 1)) {
                        scarchiving += "'" + scarchiving2[i];
                    } else if (i == 0) {
                        scarchiving += scarchiving2[i] + "'" + ",";
                    } else {
                        scarchiving += "'" + scarchiving2[i] + "'" + ",";
                    }
                }
            }
            params.put("scarchiving", " ('" + scarchiving + "')");
        }
        if (StringUtil.isNotEmpty(info.getDoctor())) {
            String doctor = "";
            String doctor2[] = info.getDoctor().split(",");
            if (doctor2.length == 1) {
                doctor = doctor2[0];
            } else {
                for (int i = 0; i < doctor2.length; i++) {
                    if (i == (doctor2.length - 1)) {
                        doctor += "'" + doctor2[i];
                    } else if (i == 0) {
                        doctor += doctor2[i] + "'" + ",";
                    } else {
                        doctor += "'" + doctor2[i] + "'" + ",";
                    }
                }
            }
            params.put("doctor", " ('" + doctor + "')");
        }
        if (StringUtil.isNotEmpty(info.getTcName())) {
            String tcName = "";
            String tcName2[] = info.getTcName().split(",");
            if (tcName2.length == 1) {
                tcName = tcName2[0];
            } else {
                for (int i = 0; i < tcName2.length; i++) {
                    if (i == (tcName2.length - 1)) {
                        tcName += "'" + tcName2[i];
                    } else if (i == 0) {
                        tcName += tcName2[i] + "'" + ",";
                    } else {
                        tcName += "'" + tcName2[i] + "'" + ",";
                    }
                }
            }
            params.put("tcName", " ('" + tcName + "')");
        }
        String tableHead = info.getAbbreviation();
        String tableName1 = tableHead + "deal_info";
        String tableName2 = tableHead + "recharge_info";
        params.put("tableName", tableHead+"customer_info");
        params.put("tableName1", tableName1);
        params.put("tableName2", tableName2);
        params.put("startIndex", info.getStartIndex());
        params.put("limitIndex", info.getLimitIndex());
        return dao.getperformanceTableExport(params);
    }

    public void performanceTableExport(SearchPerformanceInfo searchInfo, HttpServletResponse response) {
        try {
            List<PerformanceInfo> list = this.getperformanceTableExport(searchInfo);
            // 解析成excel文件
            // 第一步，创建一个webbook，对应一个Excel文件
            HSSFWorkbook wb = new HSSFWorkbook();
            // 第二步，在wb中添加一个sheet,对应Excel文件中的sheet
            HSSFSheet sheet = wb.createSheet("前端导出");
            for (int i = 0; i < 32; i++) {
                sheet.setColumnWidth(i, 24 * 256);
            }
            // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
            HSSFRow row = sheet.createRow(0);
            // 第四步，创建单元格，并设置值表头 设置表头居中
            HSSFCellStyle style = wb.createCellStyle();
            style.setAlignment(HorizontalAlignment.CENTER);// 创建一个居中格式
            HSSFCell cell = row.createCell(0);
            cell.setCellValue("病历号");
            cell.setCellStyle(style);
            cell = row.createCell(1);
            cell.setCellValue("客户姓名");
            cell.setCellStyle(style);
            cell = row.createCell(2);
            cell.setCellValue("电话");
            cell.setCellStyle(style);
            cell = row.createCell(3);
            cell.setCellValue("性别");
            cell.setCellStyle(style);
            cell = row.createCell(4);
            cell.setCellValue("年龄");
            cell.setCellStyle(style);
            cell = row.createCell(5);
            cell.setCellValue("到店时间");
            cell.setCellStyle(style);
            cell = row.createCell(6);
            cell.setCellValue("结账时间");
            cell.setCellStyle(style);
            cell = row.createCell(7);
            cell.setCellValue("新老客");
            cell.setCellStyle(style);
            cell = row.createCell(8);
            cell.setCellValue("交易类型");
            cell.setCellStyle(style);
            cell = row.createCell(9);
            cell.setCellValue("科室");
            cell.setCellStyle(style);
            cell = row.createCell(10);
            cell.setCellValue("应收");
            cell.setCellStyle(style);
            cell = row.createCell(11);
            cell.setCellValue("实收");
            cell.setCellStyle(style);
            cell = row.createCell(12);
            cell.setCellValue("实到金额");
            cell.setCellStyle(style);
            cell = row.createCell(13);
            cell.setCellValue("储值划扣");
            cell.setCellStyle(style);
            cell = row.createCell(14);
            cell.setCellValue("优惠抵扣");
            cell.setCellStyle(style);
            cell = row.createCell(15);
            cell.setCellValue("全院业绩");
            cell.setCellStyle(style);
            cell = row.createCell(16);
            cell.setCellValue("科室业绩");
            cell.setCellStyle(style);
            cell = row.createCell(17);
            cell.setCellValue("一级渠道");
            cell.setCellStyle(style);
            cell = row.createCell(18);
            cell.setCellValue("二级渠道");
            cell.setCellStyle(style);
            cell = row.createCell(19);
            cell.setCellValue("三级渠道");
            cell.setCellStyle(style);
            cell = row.createCell(20);
            cell.setCellValue("一级媒介");
            cell.setCellStyle(style);
            cell = row.createCell(21);
            cell.setCellValue("二级媒介");
            cell.setCellStyle(style);
            cell = row.createCell(22);
            cell.setCellValue("三级媒介");
            cell.setCellStyle(style);
            cell = row.createCell(23);
            cell.setCellValue("TC");
            cell.setCellStyle(style);
            cell = row.createCell(24);
            cell.setCellValue("推荐人");
            cell.setCellStyle(style);
            cell = row.createCell(25);
            cell.setCellValue("护士");
            cell.setCellStyle(style);
            cell = row.createCell(26);
            cell.setCellValue("门店");
            cell.setCellStyle(style);
            cell = row.createCell(27);
            cell.setCellValue("网电咨询师");
            cell.setCellStyle(style);
            cell = row.createCell(28);
            cell.setCellValue("正畸编号");
            cell.setCellStyle(style);
            cell = row.createCell(29);
            cell.setCellValue("转诊人");
            cell.setCellStyle(style);
            cell = row.createCell(30);
            cell.setCellValue("被转诊人");
            cell.setCellStyle(style);
            cell = row.createCell(31);
            cell.setCellValue("转诊时间");
            cell.setCellStyle(style);
            // 填充数据
            for (int i = 0; i < list.size(); i++) {
                PerformanceInfo map = list.get(i);
                row = sheet.createRow(i + 1);
                // 第四步，创建单元格，并设置值
                for (int j = 0; j <= 32; j++) {
                    row.createCell(j).setCellStyle(style);
                }
                row.getCell(0).setCellValue(map.getMedicalRecordNumber());
                row.getCell(1).setCellValue(map.getCustomerName());
                row.getCell(2).setCellValue(map.getCustomerPhone());
                row.getCell(3).setCellValue(map.getSex());
                row.getCell(4).setCellValue(map.getCustomerAge());
                row.getCell(5).setCellValue(map.getArrivalTime());
                row.getCell(6).setCellValue(map.getRechargeTime());
                row.getCell(7).setCellValue(map.getCustomerStatus());
                if (map.getIsDo() == 3) {
                    row.getCell(8).setCellValue("还欠款");
                } else if (map.getIsDo() == 4) {
                    row.getCell(8).setCellValue("退款");
                } else if (map.getIsDo() == 5) {
                    row.getCell(8).setCellValue("消费");
                } else if (map.getIsDo() == 9) {
                    row.getCell(8).setCellValue("储值变动");
                } else {
                    row.getCell(8).setCellValue("其他");
                }
                row.getCell(9).setCellValue(map.getTreatmentUnit());
                if (map.getIsDo() == 5) {  //财务应收=应收-本次优惠抵扣
                    row.getCell(10).setCellValue(map.getServiceActualPay()+map.getAccountArrears2()
                            +map.getDeductionAmount()+map.getDepositAmount());
                } else if (map.getIsDo() == 3) {   //财务应收=0
                    row.getCell(10).setCellValue(0);
                } else if (map.getIsDo() == 4) {  //财务应收=实收+欠费变动
                    row.getCell(10).setCellValue(map.getServiceActualPay()+map.getAccountArrears2()
                            +map.getDeductionAmount()+map.getDepositAmount());
                } else if (map.getIsDo() == 9) {  //财务应收=实收
                    row.getCell(10).setCellValue(0);
                }
                row.getCell(11).setCellValue(map.getServiceActualPay());  //实收
                row.getCell(12).setCellValue(map.getServiceActualPay()*(map.getEquation()/100)); //实到金额
                row.getCell(13).setCellValue(map.getDepositAmount());
                row.getCell(14).setCellValue(map.getDeductionAmount());
                row.getCell(15).setCellValue(map.getServiceActualPay());
                row.getCell(16).setCellValue(map.getServiceActualPay()+map.getDepositAmount());
                row.getCell(17).setCellValue(map.getOneChannel());
                row.getCell(18).setCellValue(map.getTwoChannel());
                row.getCell(19).setCellValue(map.getThrChannel());
                row.getCell(20).setCellValue(map.getOneMedium());
                row.getCell(21).setCellValue(map.getTwoMedium());
                row.getCell(22).setCellValue(map.getThrMedium());
                row.getCell(23).setCellValue(map.getTcName());
                row.getCell(24).setCellValue(map.getReferee());
                row.getCell(25).setCellValue(map.getNurse());
                row.getCell(26).setCellValue(map.getOutpatientName());
                row.getCell(27).setCellValue(map.getScarchiving());
                row.getCell(28).setCellValue(map.getOrthodonticNumber());
                row.getCell(29).setCellValue(map.getReferralerPerson());
                row.getCell(30).setCellValue(map.getReferralerPersoned());
                row.getCell(31).setCellValue(map.getReferralerTime());

            }
            String date = new SimpleDateFormat("yyyyMMddHHmmssSSS").format(new java.util.Date());
            String filename = "data" + date + ".xls";
            response.setHeader("Content-Disposition",
                    "attachment; filename=\"" + URLEncoder.encode(filename, "UTF-8") + "\"");
            response.setContentType("application/octet-stream;charset=UTF-8");
            OutputStream outputStream = new BufferedOutputStream(response.getOutputStream());
            wb.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public List<Map<String, Object>> report(SearchCashDailyReportInfo searchInfo, String obj) {
        String tableHead = searchInfo.getAbbreviation();
        String tableName1 = tableHead + "recharge_info";
        if ("hospital".equals(obj)) {
            String sqlj = "";
            if (searchInfo.getStartTime() != null && searchInfo.getStartTime() != "") {
                sqlj += " and t.recharge_time >= '" + searchInfo.getStartTime() + "' ";
            }
            if (searchInfo.getEndTime() != null && searchInfo.getEndTime() != "") {
                sqlj += " and t.recharge_time < '" + searchInfo.getEndTime() + "' ";
            }
            String aa = "";
            if (searchInfo.getEquation() == 2) {
                aa = "*(a.equation/100)";
            }
            String getsql = "select t.checkout_method from " + tableName1 + " t left join checkout_method_info t1 on t.checkout_method_id=t1.id " +
                    "where 1=1 " + sqlj + " and t1.charge_bill_is_inCash=1 group by t.checkout_method order by t1.num ";
            List<CheckoutMethodInfo> list1 = sqlMapper.selectList(getsql, CheckoutMethodInfo.class);
            String timetpye;
            if ("0".equals(searchInfo.getSettleAccounts())) {
                timetpye = " left(a.recharge_time,10) ";
            } else {
                timetpye = " a.recharges_time ";
            }
            String sql = "select " + timetpye + " as rechargeTime,";
            if (list1.size() > 0) {
                for (int i = 0; i < list1.size(); i++) {
                    sql += " sum(case when a.checkout_method='" + list1.get(i).getCheckoutMethod() + "' then  a.actual_account_balance" + aa + " else 0 end) as '" + list1.get(i).getCheckoutMethod() + "',";
                }
            }
            sql += " sum(case when a.checkout_method in (" + getsql + ") then  a.actual_account_balance" + aa + " else 0 end) as totalDailyIncome from " + tableName1 + " a where 1=1 ";
            if (searchInfo.getStartTime() != null && searchInfo.getStartTime() != "") {
                sql += " and a.recharge_time >= '" + searchInfo.getStartTime() + "' ";
            }
            if (searchInfo.getEndTime() != null && searchInfo.getEndTime() != "") {
                sql += " and a.recharge_time < '" + searchInfo.getEndTime() + "' ";
            }
            sql += " group by " + timetpye + " union all select '合   计' as rechargeTime,";
            if (list1.size() > 0) {
                for (int i = 0; i < list1.size(); i++) {
                    sql += " sum(case when a.checkout_method='" + list1.get(i).getCheckoutMethod() + "' then  a.actual_account_balance" + aa + " else 0 end) as '" + list1.get(i).getCheckoutMethod() + "',";
                }
            }
            sql += " sum(case when a.checkout_method in (" + getsql + ") then  a.actual_account_balance" + aa + " else 0 end) as totalDailyIncome from " + tableName1 + " a where 1=1 ";
            if (searchInfo.getStartTime() != null && searchInfo.getStartTime() != "") {
                sql += " and a.recharge_time >= '" + searchInfo.getStartTime() + "' ";
            }
            if (searchInfo.getEndTime() != null && searchInfo.getEndTime() != "") {
                sql += " and a.recharge_time < '" + searchInfo.getEndTime() + "' ";
            }
            List<Map<String, Object>> list = sqlMapper.selectList(sql);
            return list;
        }
        if ("departmental".equals(obj)) {
            String sqlj = "";
            if (searchInfo.getStartTime() != null && searchInfo.getStartTime() != "") {
                sqlj += " and t.recharge_time >= '" + searchInfo.getStartTime() + "' ";
            }
            if (searchInfo.getEndTime() != null && searchInfo.getEndTime() != "") {
                sqlj += " and t.recharge_time < '" + searchInfo.getEndTime() + "' ";
            }
            String aa = "";
            if (searchInfo.getEquation() == 2) {
                aa = "*(a.equation/100)";
            }
            String getsql = "select t.checkout_method from " + tableName1 + " t left join checkout_method_info t1 on t.checkout_method_id=t1.id " +
                    "where 1=1 " + sqlj + " and ( t1.type=0  or t1.type=3)  group by t.checkout_method order by t1.num ";
            List<CheckoutMethodInfo> list1 = sqlMapper.selectList(getsql, CheckoutMethodInfo.class);
            String timetpye;
            if ("0".equals(searchInfo.getSettleAccounts())) {
                timetpye = " left(a.recharge_time,10) ";
            } else {
                timetpye = " a.recharges_time ";
            }
            String sql = "select " + timetpye + " as rechargeTime,";
            if (list1.size() > 0) {
                for (int i = 0; i < list1.size(); i++) {
                    sql += " sum(case when a.checkout_method='" + list1.get(i).getCheckoutMethod() + "' then  a.actual_account_balance" + aa + " else 0 end) as '" + list1.get(i).getCheckoutMethod() + "',";
                }
            }
            sql += " sum(case when a.checkout_method in (" + getsql + ") then  a.actual_account_balance" + aa + " else 0 end) as totalDailyIncome from " + tableName1 + " a where 1=1 and (a.isEarnes=1 or a.isEarnes=5) ";
            if (searchInfo.getStartTime() != null && searchInfo.getStartTime() != "") {
                sql += " and a.recharge_time >= '" + searchInfo.getStartTime() + "' ";
            }
            if (searchInfo.getEndTime() != null && searchInfo.getEndTime() != "") {
                sql += " and a.recharge_time < '" + searchInfo.getEndTime() + "' ";
            }
            sql += " group by " + timetpye + " union all select '合   计' as rechargeTime,";
            if (list1.size() > 0) {
                for (int i = 0; i < list1.size(); i++) {
                    sql += " sum(case when a.checkout_method='" + list1.get(i).getCheckoutMethod() + "' then  a.actual_account_balance" + aa + " else 0 end) as '" + list1.get(i).getCheckoutMethod() + "',";
                }
            }
            sql += " sum(case when a.checkout_method in (" + getsql + ") then  a.actual_account_balance" + aa + " else 0 end) as totalDailyIncome from " + tableName1 + " a where 1=1 and (a.isEarnes=1 or a.isEarnes=5) ";
            if (searchInfo.getStartTime() != null && searchInfo.getStartTime() != "") {
                sql += " and a.recharge_time >= '" + searchInfo.getStartTime() + "' ";
            }
            if (searchInfo.getEndTime() != null && searchInfo.getEndTime() != "") {
                sql += " and a.recharge_time < '" + searchInfo.getEndTime() + "' ";
            }
            List<Map<String, Object>> list = sqlMapper.selectList(sql);
            return list;
        } else {
            return null;
        }
    }

}